package cnki.kg.demo;

import cnki.kg.demo.entity.CategoryNode;
import cnki.kg.demo.util.DateUtil;
import cnki.kg.demo.util.StringUtil;
import org.junit.jupiter.api.Test;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.test.context.web.WebAppConfiguration;

import java.io.IOException;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;


@SpringBootTest
@WebAppConfiguration
public class CreateSZRWCategoryFromTable {
    @Autowired
    @Qualifier("mysqlJdbcTemplate")
    JdbcTemplate mysqlJdbcTemplate;
    @Autowired
    @Qualifier("ctwhJdbcTemplate")
    JdbcTemplate ctwhJdbcTemplate;
    @Autowired
    @Qualifier("szrwJdbcTemplate")
    JdbcTemplate szrwJdbcTemplate;
    @Test
    public void updateCdSort(){
        String cq = String.format("select  c_dynasty_chn,c_sort from `朝代_简_copy` where  isdelete=0");
        List<Map<String, Object>> maps = szrwJdbcTemplate.queryForList(cq);
        for (Map<String, Object> mp : maps) {
            if (mp.get("c_dynasty_chn") != null) {
                String code = mp.get("c_dynasty_chn").toString();
                String updateSql = String.format("update categorynode set SortCode='%s' where CategoryNodeName='%s'",mp.get("c_sort"), code);
                mysqlJdbcTemplate.execute(updateSql);
                System.out.println(code);
            }
        }
    }
    @Test
    public void updateGzSort(){
        String cq = String.format("select c_office_type_desc_chn,c_office_sort from 官职表_new where c_office_sort is not null");
        List<Map<String, Object>> maps = szrwJdbcTemplate.queryForList(cq);
        for (Map<String, Object> mp : maps) {
            if (mp.get("c_office_type_desc_chn") != null) {
                String code = mp.get("c_office_type_desc_chn").toString();
                String updateSql = String.format("update categorynode set SortCode='%s' where CategoryNodeName='%s'",mp.get("c_office_sort"), code);
                mysqlJdbcTemplate.execute(updateSql);
                System.out.println(code);
            }
        }
    }
    @Test
    public void updateRwbd(){
        String cq = String.format("select * from  categorynode where CategoryNodeCode like '0001%' and IsLeaf =1");
        List<Map<String, Object>> maps = szrwJdbcTemplate.queryForList(cq);
        for (Map<String, Object> mp : maps) {
            if (mp.get("c_office_type_desc_chn") != null) {
                String code = mp.get("c_office_type_desc_chn").toString();
                String updateSql = String.format("update categorynode set SortCode='%s' where CategoryNodeName='%s'",mp.get("c_office_sort"), code);
                mysqlJdbcTemplate.execute(updateSql);
                System.out.println(code);
            }
        }
    }
    @Test
    public void createRw(){
        String cq = String.format("SELECT c_personid,gjs_entry_id,c_name_chn,c_name,c_birthyear,c_deathyear,c_dynasty_chn,c_dy,c_dy_type,c_parentid,c_status_desc_chn,c_status_code,c_status_type_name,c_status_type_code from `人物基本表_new` where c_dynasty_chn is not  null and isdelete=0");
        List<Map<String, Object>> mapList = szrwJdbcTemplate.queryForList(cq);
        for (Map<String, Object> n : mapList) {
            if(n.get("c_dy_type")==null){
                n.replace("c_dy_type","未详");
            }
            if(n.get("c_dynasty_chn")==null){
                n.replace("c_dynasty_chn","未详");
            }
            if(n.get("c_status_type_name")==null){
                n.replace("c_status_type_name","未详");
            }
            if(n.get("c_status_desc_chn")==null){
                n.replace("c_status_desc_chn","未详");
            }
            if(n.get("gjs_entry_id")==null){
                n.replace("gjs_entry_id","");
            }
            if(n.get("c_personid")==null){
                n.replace("c_personid","");
            }

        }
        int total=mapList.size();
        int i=0;
        List<Map<String, Object>> maps=mapList;
        List<String> cdList = maps.stream().map(n -> n.get("c_dy_type").toString()).distinct().collect(Collectors.toList());
        if (cdList == null || cdList.size() == 0) return;
        for (String cdfirst : cdList) {
            CategoryNode submitItem = new CategoryNode();
            submitItem.setCategoryNodeName(cdfirst);
            submitItem.setParentid(1);
            submitItem.setBirth(0);
            submitItem.setCategoryTypeID(1);
            submitItem.setDeath(0);
            submitItem.setPinYin("");
            submitItem.setLevel(1);
            submitItem.setIsLeaf(1);
            submitItem.setFormula("");
            Integer cdFirstId=0;
            List<String> cdChildList = maps.stream().filter(n -> n.get("c_dy_type")!=null&&n.get("c_dy_type").equals(cdfirst)).map(n -> n.get("c_dynasty_chn").toString()).distinct().collect(Collectors.toList());
            if(cdChildList.size()==0&&cdfirst.equals("未详")){
                continue;
            }
            if(cdChildList.size()>0){
                submitItem.setIsLeaf(0);
                i++;
                cdFirstId = insertCategoryNode(submitItem,i,total);
            }
            for (String cdSecond : cdChildList) {
                CategoryNode submitItem2 = new CategoryNode();
                submitItem2.setCategoryNodeName(cdSecond);
                submitItem2.setParentid(cdFirstId);
                submitItem2.setCategoryTypeID(1);
                submitItem2.setLevel(2);
                submitItem2.setBirth(0);
                submitItem2.setDeath(0);
                submitItem2.setPinYin("");
                submitItem2.setIsLeaf(1);
                submitItem2.setFormula("");
                List<String> dwList = maps.stream().filter(n -> n.get("c_dy_type")!=null&&n.get("c_dy_type").toString().equals(cdfirst) && n.get("c_dynasty_chn")!=null&&n.get("c_dynasty_chn").toString().equals(cdSecond)).map(n -> n.get("c_status_type_name").toString()).distinct().collect(Collectors.toList());
                Integer cdSecondId=0;
                if(dwList.size()==0&&cdSecond.equals("未详")){
                    continue;
                }
                if(dwList.size()>0){
                    submitItem2.setIsLeaf(0);
                    i++;
                    cdSecondId = insertCategoryNode(submitItem2,i,total);
                }
                for (String dwFirst : dwList) {
                    CategoryNode submitItem3 = new CategoryNode();
                    submitItem3.setCategoryNodeName(dwFirst);
                    submitItem3.setParentid(cdSecondId);
                    submitItem3.setBirth(0);
                    submitItem3.setCategoryTypeID(1);
                    submitItem3.setDeath(0);
                    submitItem3.setPinYin("");
                    submitItem3.setLevel(3);
                    submitItem3.setIsLeaf(1);
                    submitItem3.setFormula("");
                    List<String> dwChildList = maps.stream().filter(n -> n.get("c_dy_type").toString().equals(cdfirst)
                            && n.get("c_dynasty_chn").toString().equals(cdSecond)
                            && n.get("c_status_type_name").toString().equals(dwFirst)
                    ).map(n -> n.get("c_status_desc_chn").toString()).distinct().collect(Collectors.toList());
                    Integer dwFirstId=0;
                    if(dwChildList.size()==0&&dwFirst.equals("未详")){
                        continue;
                    }
                    if(dwChildList.size()>0){
                        submitItem3.setIsLeaf(0);
                        i++;
                        dwFirstId = insertCategoryNode(submitItem3,i,total);
                    }

                    for (String dwSecond : dwChildList) {
                        CategoryNode submitItem4 = new CategoryNode();
                        submitItem4.setCategoryNodeName(dwSecond);
                        submitItem4.setParentid(dwFirstId);
                        submitItem4.setLevel(4);
                        submitItem4.setCategoryTypeID(1);
                        submitItem4.setIsLeaf(0);
                        submitItem4.setBirth(0);
                        submitItem4.setDeath(0);
                        submitItem4.setPinYin("");
                        submitItem4.setFormula("");
                        List<Map<String, Object>> rwList = maps.stream().filter(n ->n.get("c_dy_type").toString().equals(cdfirst)
                                && n.get("c_dynasty_chn").toString().equals(cdSecond)
                                && n.get("c_status_type_name").toString().equals(dwFirst)
                                &&n.get("c_status_desc_chn").toString().equals(dwSecond)
                        ).map(n -> {
                            Map<String, Object> mp=new HashMap<>();
                            mp.put("name",n.get("c_name_chn").toString());
                            mp.put("pinyin",n.get("c_name")!=null?n.get("c_name").toString():"");
                            Integer brith=0;
                            if(n.get("c_birthyear")!=null){
                                if(StringUtil.isNumeric(n.get("c_birthyear").toString())){
                                    Double a= Math.floor(Double.parseDouble(n.get("c_birthyear").toString()));
                                    brith=a.intValue();
                                }
                            }
                            mp.put("brith",brith);
                            Integer death=0;
                            if(n.get("c_deathyear")!=null){
                                if(StringUtil.isNumeric(n.get("c_deathyear").toString())){
                                    Double a= Math.floor(Double.parseDouble(n.get("c_deathyear").toString()));
                                    death=a.intValue();
                                }
                            }
                            mp.put("death",death);//c_personid,gjs_entry_id
                            mp.put("uuid",n.get("c_personid").toString());
                            mp.put("gjs_entry_id",n.get("gjs_entry_id").toString());
                            mp.put("lable","humantity_persons");
                            return mp;
                        }).distinct().collect(Collectors.toList());
                        Integer dwSecondId=0;
                        if(rwList.size()==0&&rwList.equals("未详")){
                            continue;
                        }
                        if(rwList.size()>0){
                            submitItem4.setIsLeaf(0);
                            i++;
                            dwSecondId = insertCategoryNode(submitItem4,i,total);
                        }

                        for (Map<String, Object> rw : rwList) {
                            CategoryNode submitItem5 = new CategoryNode();
                            submitItem5.setCategoryNodeName(rw.get("name").toString());
                            submitItem5.setParentid(dwSecondId);
                            submitItem5.setLevel(5);
                            submitItem5.setIsLeaf(1);
                            submitItem5.setCategoryTypeID(1);
                            submitItem5.setBirth(Integer.parseInt(rw.get("brith").toString()));
                            submitItem5.setDeath(Integer.parseInt(rw.get("death").toString()));
                            submitItem5.setPinYin(rw.get("pinyin").toString());
                            submitItem5.setUuid(rw.get("uuid").toString());
                            submitItem5.setEntityId(rw.get("gjs_entry_id").toString());
                            submitItem5.setLabel(rw.get("lable").toString());
                            submitItem5.setFormula(String.format("主题=\\'%s\\'", rw.get("name").toString()));
                            i++;
                            insertCategoryNode(submitItem5,i,total);
                        }
                    }
                }
            }
        }
    }

    @Test
    public void createGz() {
        String cq = String.format("select gjs_entry_id,c_dy,c_office_type_node_id,c_parent_id,c_office_type_desc_chn from 官职表_new ");
        List<Map<String, Object>> maps = szrwJdbcTemplate.queryForList(cq);
        List<Map<String, Object>> parentMaps = maps.stream().filter(n -> n.get("c_parent_id") != null && n.get("c_parent_id").equals("0") && null != n.get("c_dy")).collect(Collectors.toList());
        int index=0;
        for (Map<String, Object> map : parentMaps) {
            String pid = map.get("c_office_type_node_id")!=null?map.get("c_office_type_node_id").toString():"";
            String cd = map.get("c_office_type_desc_chn")!=null?map.get("c_office_type_desc_chn").toString():"";
            String entityId = map.get("gjs_entry_id")!=null?map.get("gjs_entry_id").toString():"";
            createChildGz(2, cd,1,pid, maps,entityId,index);
        }
    }

    private void createChildGz(Integer nodeId, String nodeName, Integer level, String sourceId, List<Map<String, Object>> maps,String entityId,int index) {
        List<Map<String, Object>> childList = maps.stream().filter(n -> n.get("c_parent_id").toString().equals(sourceId)).collect(Collectors.toList());
        CategoryNode submitItem = new CategoryNode();
        String pSubject = String.format("主题=\\'%s\\'", nodeName);
        submitItem.setCategoryNodeName(nodeName);
        submitItem.setParentid(nodeId);
        submitItem.setLevel(level);
        submitItem.setCategoryTypeID(1);
        submitItem.setIsLeaf(1);
        submitItem.setSortCode(0);
        submitItem.setUuid(sourceId);
        submitItem.setLabel("humentity_office");
        submitItem.setEntityId(entityId);

        submitItem.setBirth(0);
        submitItem.setDeath(0);
        submitItem.setPinYin("");


        if (childList.size() > 0) {
            submitItem.setIsLeaf(0);
            String words = String.join("+", childList.stream().filter(n -> n.get("c_office_type_node_id").toString().startsWith(sourceId)).map(n -> String.format("\\'%s\\'", n.get("c_office_type_desc_chn"))).collect(Collectors.toList()));
            pSubject = String.format("主题=%s", words);
        }
        submitItem.setFormula(pSubject);
        index++;
        Integer id = insertCategoryNode(submitItem,index,maps.size());
        if (childList.size() > 0) {
            for (Map<String, Object> mp : childList) {
                String eId = mp.get("gjs_entry_id")!=null?mp.get("gjs_entry_id").toString():"";
                createChildGz(id, mp.get("c_office_type_desc_chn").toString(), level + 1, mp.get("c_office_type_node_id").toString(), maps,eId,index);
            }
        }
    }

    @Test
    public void createCd() {
        String cq = String.format("select c_dy,c_dynasty_chn,c_parentid,c_sort  from 朝代_简_copy where isdelete=0 ");
        List<Map<String, Object>> maps = szrwJdbcTemplate.queryForList(cq);
        List<Map<String, Object>> parentMaps = maps.stream().filter(n -> n.get("c_parentid").equals(0)).collect(Collectors.toList());
        int i=0;int total=0;
        for (Map<String, Object> map : parentMaps) {
            Integer pid = Integer.parseInt(map.get("c_dy").toString());
            String cd = map.get("c_dynasty_chn").toString();
            Double sort = Double.parseDouble(map.get("c_sort").toString());
            List<Map<String, Object>> childList = maps.stream().filter(n -> n.get("c_parentid").equals(pid)).collect(Collectors.toList());
            String pSubject = String.join("+", childList.stream().map(n -> String.format("\\'%s\\'", n.get("c_dynasty_chn"))).collect(Collectors.toList()));
            CategoryNode submitItem = new CategoryNode();
            submitItem.setCategoryNodeName(cd);
            submitItem.setParentid(3);
            submitItem.setLevel(1);
            submitItem.setCategoryTypeID(1);
            submitItem.setIsLeaf(0);
            submitItem.setSortCode(sort);
            submitItem.setUuid("");
            submitItem.setLabel("");
            submitItem.setEntityId("");

            submitItem.setBirth(0);
            submitItem.setDeath(0);
            submitItem.setPinYin("");
            if (childList.size() > 0) {
                submitItem.setFormula(String.format("主题=%s", pSubject));
            } else {
                submitItem.setFormula(String.format("主题=\\'%s\\'", cd));
            }
            i++;
            Integer id = insertCategoryNode(submitItem,i,total);
            for (Map<String, Object> m : childList) {
                CategoryNode submitItemChild = new CategoryNode();
                submitItemChild.setCategoryNodeName(m.get("c_dynasty_chn").toString());
                submitItemChild.setParentid(id);
                submitItemChild.setLevel(2);
                submitItemChild.setIsLeaf(1);
                submitItemChild.setUuid("");
                submitItemChild.setLabel("");
                submitItemChild.setEntityId("");

                submitItemChild.setBirth(0);
                submitItemChild.setDeath(0);
                submitItemChild.setPinYin("");
                submitItemChild.setCategoryTypeID(1);
                submitItemChild.setSortCode(Float.parseFloat(m.get("c_sort").toString()));
                submitItemChild.setFormula(String.format("主题=\\'%s\\'", m.get("c_dynasty_chn").toString()));
                i++;
                insertCategoryNode(submitItemChild,i,total);
            }
        }
    }

    @Test
    public void createFormula() throws IOException {
        //updateFormula("0001");
        //updateFormula("0002");
        updateFormula("0003");
    }

    private void updateFormula(String code) {
        String cq = String.format("select CategoryNodeName,CategoryNodeCode,CategoryNodeID,`level` from categorynode where CategoryNodeCode like('%s') and  (formulaLunwen is null or formulaLunwen='' )", code + "%");
        List<Map<String, Object>> maps = mysqlJdbcTemplate.queryForList(cq);
        for (Map<String, Object> map : maps) {
            String CategoryNodeName = map.get("CategoryNodeName").toString();
            String CategoryNodeCode = map.get("CategoryNodeCode").toString();
            String CategoryNodeID = map.get("CategoryNodeID").toString();
            String level = map.get("level").toString();
            String cq2 = String.format("select distinct(CategoryNodeName) as name from categorynode where CategoryNodeCode like('%s') and  CategoryNodeID!=%s and level=3", CategoryNodeCode + "%", CategoryNodeID);
            List<Map<String, Object>> map2s = mysqlJdbcTemplate.queryForList(cq2);
            List<String> names = map2s.stream().map(n -> String.format("\\'%s\\'", n.get("name").toString())).collect(Collectors.toList());
            String words = String.join("+", names);
            String updateSql = String.format("update categorynode set formulaLunwen='%s' where CategoryNodeID=%s", String.format(" 主题=%s", words), CategoryNodeID);
            mysqlJdbcTemplate.execute(updateSql);
        }

    }

    private Integer insertCategoryNode(CategoryNode submitItem,int index,int total) {
        String sql = String.format(" insert into categorynode " +
                        "(uuid,entityId,label,parentid,level,IsLeaf,Status,SortCode,CreateUser,CreateTime,ModifyUser,ModifyTime,CategoryTypeID,CategoryNodeName,formulaLunwen,birth,death,pinyin)" +
                        " values('%s','%s','%s',%s,%s,%s,%s,%s,'%s','%s','%s','%s',%s,'%s','%s',%s,'%s','%s')",
                submitItem.getUuid(),submitItem.getEntityId(),submitItem.getLabel(),submitItem.getParentid(), submitItem.getLevel(), submitItem.getIsLeaf(), 1, submitItem.getSortCode(), "sa", DateUtil.getNowDateHms(), "sa", DateUtil.getNowDateHms(),
                submitItem.getCategoryTypeID(), submitItem.getCategoryNodeName(), submitItem.getFormula(),submitItem.getBirth(),submitItem.getDeath(),submitItem.getPinYin()
        );
        KeyHolder keyHolder = new GeneratedKeyHolder();
        mysqlJdbcTemplate.update(new PreparedStatementCreator() {
                                     @Override
                                     public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
                                         PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                                         return ps;
                                     }
                                 },
                keyHolder);
        Integer id = keyHolder.getKey().intValue();
        if (submitItem.getParentid() == 0 || submitItem.getParentid() == null) {
            String code = String.format("%04d", id);
            String updateSql = String.format("update categorynode set CategoryNodeCode='%s' where CategoryNodeID=%s", code, id);
            mysqlJdbcTemplate.execute(updateSql);
            //System.out.println(String.format("%s_%s", code, submitItem.getCategoryNodeName()));
        } else {
            String cq = String.format("select CategoryNodeCode from categorynode where CategoryNodeID=%s", submitItem.getParentid());
            Map<String, Object> mp = mysqlJdbcTemplate.queryForMap(cq);
            if (mp.get("CategoryNodeCode") != null) {
                String code = mp.get("CategoryNodeCode") + String.format("%04d", id);
                String updateSql = String.format("update categorynode set CategoryNodeCode='%s' where CategoryNodeID=%s", code, id);
                mysqlJdbcTemplate.execute(updateSql);
                //System.out.println(String.format("%s_%s", code, submitItem.getCategoryNodeName()));
            }
        }
        System.out.println(String.format("%s/%s",index,total));
        return id;
    }

    @Test
    public void createCtwhZT() throws IOException {
        String level0 = "select code,name,isleaf from ctwhcategory where grade=1";
        List<Map<String, Object>> maps = ctwhJdbcTemplate.queryForList(level0);
        int i=0;
        for (Map<String, Object> map : maps) {
            String name = map.get("name").toString();
            String code = map.get("code").toString();
            CategoryNode submitItem = new CategoryNode();
            submitItem.setCategoryNodeName(name);
            submitItem.setParentid(0);
            submitItem.setLevel(0);
            submitItem.setIsLeaf(0);
            submitItem.setUuid("");
            submitItem.setLabel("");
            submitItem.setEntityId("");

            submitItem.setBirth(0);
            submitItem.setDeath(0);
            submitItem.setPinYin("");
            submitItem.setCategoryTypeID(2);
            String format = String.format("主题=\\'%s\\'", name);
            submitItem.setFormula(format);
            i++;
            Integer id = insertCategoryNode(submitItem,i,maps.size());
            createChild(code, 1, id);
        }
    }

    public void createChild(String code, int level, int pid) {
        String level0 = String.format("select code,name,isleaf from ctwhcategory where parentcode='%s' and code!='%s'", code, code);
        List<Map<String, Object>> maps = ctwhJdbcTemplate.queryForList(level0);
        if (maps != null && maps.size() > 0) {
            for (Map<String, Object> map : maps) {
                String nodeCode = map.get("code").toString();
                String nodeName = map.get("name").toString();
                String isleaf = map.get("isleaf").toString();
                CategoryNode submitItem = new CategoryNode();
                submitItem.setCategoryNodeName(nodeName);
                submitItem.setParentid(pid);
                submitItem.setLevel(level);
                submitItem.setIsLeaf(Integer.parseInt(isleaf));
                submitItem.setUuid("");
                submitItem.setLabel("");
                submitItem.setEntityId("");

                submitItem.setBirth(0);
                submitItem.setDeath(0);
                submitItem.setPinYin("");
                submitItem.setCategoryTypeID(2);

                String format = String.format("主题=\\'%s\\'", nodeName);
                submitItem.setFormula(format);
                Integer id = insertCategoryNode(submitItem,0,0);
                if (isleaf.equals("1")) continue;
                createChild(nodeCode, level + 1, id);
            }
        }
    }

    @Test
    public void updateSortCode() throws IOException {
        String level0 = "select c_dynasty_chn as cd,c_sort as sortcode from `朝代_简` ORDER BY c_sort ";
        List<Map<String, Object>> maps = szrwJdbcTemplate.queryForList(level0);
        for (Map<String, Object> map : maps) {
            Object mp = map.get("cd");
            Object mp2 = map.get("sortcode");
            if (mp == null || mp2 == null) continue;
            String cd = map.get("cd").toString();
            String sortcode = new BigDecimal(map.get("sortcode").toString()).stripTrailingZeros().toPlainString();
            String sql = String.format("update categorynode set sortCode=%s where CategoryNodeName='%s' and CategoryTypeID=1", sortcode, cd);
            mysqlJdbcTemplate.execute(sql);
        }
    }


}
